package com.kpmg.datalake.autopackage.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import javax.annotation.Resource;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.aop.framework.AopContext;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.github.pagehelper.util.StringUtil;
import com.kpmg.datalake.autopackage.dao.ExcelBasicMapper;
import com.kpmg.datalake.autopackage.dao.ExcelMapper;
import com.kpmg.datalake.autopackage.service.ExcelService;
import com.kpmg.datalake.autopackage.vo.ExcelVo;
import com.kpmg.datalake.autopackage.vo.SourceDataManagerDTO;
import com.kpmg.datalake.common.dynamicdatasource.DynamicDS;
import com.kpmg.datalake.common.dynamicdatasource.DynamicDataSourceContextHolder;
import com.kpmg.datalake.common.exception.CustomException;
import com.kpmg.datalake.common.utils.EmptyUtil;
import com.kpmg.datalake.common.utils.ExcelUtil;
import com.kpmg.datalake.common.utils.LogUtils;
import com.kpmg.datalake.common.utils.Utils;
import com.kpmg.datalake.common.vo.ServerResponse;
import com.kpmg.datalake.db.model.Project;
import com.kpmg.datalake.db.model.SysUsr;

/**
 * Excel模板管理模块
 *
 * @author James
 */
@Service
public class ExcelServiceImpl implements ExcelService {

  private static final Logger logger = LoggerFactory.getLogger(ExcelServiceImpl.class);

  @Resource
  private ExcelMapper excelMapper;

  @Resource
  private ExcelBasicMapper excelBasicMapper;


  private static final String MESSAGE = "message";
  private static final String SUCCESS = "success";
  private static final String TABLENAME = "TABLE_NAME";
  private static final String JBYJYC = "建表语句异常";
  private static final String YBYSB = "预编译关闭失败";
  private static final String JGJGBSB = "结果集关闭失败";
  private static final String SJKLJGBSB = "数据库连接关闭失败";
  private static final String DROPTBALE = "drop table ";

  private static final String TSH = "保存失败,表字段间有空白列";

  private static final String TSHTHREE = "表数据为空，保存失败";
  private static final String FILELIST = "fielCnListC";
  private static final String FILELISTC = "fielListC";
  private static final String FILETYPELIST = "fielTypeListC";
  private static final String DATALIST = "dataList";
  private static final String LIST1 = "list1";

  /**
   * Excel模板新增
   */
  @Transactional
  @Override
  public void insertClientManage(SysUsr user, ExcelVo newExc, String personalPath,
      Project project) {

    // 写入模板表KPMG_CLIENT_MANAGEMENT
    ExcelVo exc = new ExcelVo();
    String tplId = Utils.getRequestNo();
    exc.setDataId(tplId);
    exc.setUploadId(user.getUsrId());
    exc.setClientId(newExc.getClientId());
    exc.setClientNm(newExc.getClientNm());
    exc.setFileUrl(newExc.getFileUrl());
    exc.setRemark(newExc.getRemark());
    exc.setDataNm(newExc.getDataNm());
    exc.setDelInd(newExc.getDelInd());
    excelMapper.insertSelective(exc);
    logger.info("{}写入模板表", newExc.getClientId());

  }


  @Override
  public ServerResponse<PageInfo<ExcelVo>> getSourceDataList(
      SourceDataManagerDTO sourceDataManagerDto) {
    // 开始分页操作
    PageHelper.startPage(sourceDataManagerDto.getPageIndex(), sourceDataManagerDto.getPageSize());
    List<ExcelVo> sourceDataList = excelMapper.selectSourceDataList(
        sourceDataManagerDto.getInputName(), sourceDataManagerDto.getProjectId());
    PageInfo<ExcelVo> requestPageList = new PageInfo<>(sourceDataList);
    return ServerResponse.createBySuccess("获取源数据成功", requestPageList);
  }



  public Map<String, Object> checkList(List<String> list1, String tableName) {
    Map<String, Object> map = new HashMap<>();
    map.put(SUCCESS, true);
    if (!list1.contains("YEAR_PERIOD")) {
      map.put(SUCCESS, false);
      map.put(MESSAGE, "表" + tableName + "中没有YEAR_PERIOD字段模板错误");
      return map;
    }
    if (list1.contains("ISTEST")) {
      map.put(SUCCESS, false);
      map.put(MESSAGE, "表" + tableName + "中不应该包含ISTEST字段模板错误");
      return map;
    }
    if (!list1.contains("YEAR")) {
      map.put(SUCCESS, false);
      map.put(MESSAGE, "表" + tableName + "中没有YEAR字段模板错误");
      return map;
    }
    if (!list1.contains("PERIOD")) {
      map.put(SUCCESS, false);
      map.put(MESSAGE, "表" + tableName + "中没有PERIOD字段模板错误");
      return map;
    }
    if (!list1.contains("ENTITY")) {
      map.put(SUCCESS, false);
      map.put(MESSAGE, "表" + tableName + "中没有ENTITY字段模板错误");
      return map;
    }
    return map;
  }

  /**
   * 判断是否含有特殊字符
   *
   * @param str
   * @return true为包含，false为不包含
   */
  public static boolean isSpecialChar(String str) {
    String regEx = "[ `~!@#$%^&*()+=|{}':;',\\[\\].<>/?~！@#￥%……&*（）——+|{}【】‘；：”“’。，、？]|\n|\r|\t";
    Pattern p = Pattern.compile(regEx);
    Matcher m = p.matcher(str);
    return m.find();
  }

  public Map<String, Object> checkTableName(XSSFWorkbook wb) {
    Map<String, Object> map = new HashMap<>();
    map.put(SUCCESS, true);
    for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
      String tableName = wb.getSheetName(sheetIndex).toUpperCase();
      if (!tableName.startsWith("PKG_")) {
        map.put(SUCCESS, false);
        map.put(MESSAGE, "表" + tableName + "中表名没有以PKG开头模板错误");
        return map;
      }
      if (isSpecialChar(tableName)) {
        map.put(SUCCESS, false);
        map.put(MESSAGE, "表" + tableName + "中表名含有特殊字符");
        return map;
      }
      if (tableName.length() > 30) {
        map.put(SUCCESS, false);
        map.put(MESSAGE, "表" + tableName + "中表名长度超限");
        return map;
      }
    }
    return map;
  }

  /**
   * @param wb
   * @param projectId
   * @return
   */
  @DynamicDS("projectId")
  public Map<String, Object> checkTableName(XSSFWorkbook wb, String projectId) {
    return this.checkTableName(wb);
  }

  @Transactional
  public Map<String, Object> analyseFile(SysUsr user, String projectId, File file, String fileName,
      int flag) throws IOException {
    Map<String, Object> map = new HashMap<>();
    map.put(SUCCESS, true);
    InputStream is = new FileInputStream(file);
    XSSFWorkbook wb = new XSSFWorkbook(is);
    map = this.checkTableName(wb, projectId);
    if (!(boolean) map.get(SUCCESS)) {
      return map;
    }

    return map;
  }

  /**
   * @param user
   * @param projectId
   * @param file
   * @param fileName
   * @param flag
   * @return
   * @throws IOException
   */
  @Override
  public Map<String, Object> excelAnalyseXlsx(SysUsr user, String projectId, File file,
      String fileName, int flag) throws IOException {

    Map<String, Object> map = new HashMap<>();
    List<String> li = new ArrayList<>();
    map.put(SUCCESS, true);
    InputStream is = new FileInputStream(file);
    XSSFWorkbook wb = new XSSFWorkbook(is);
    map = this.checkTableName(wb, projectId);
    if (!(boolean) map.get(SUCCESS)) {
      return map;
    }

    // 新增修改
    String tableName = "";
    try {
      for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
        tableName = wb.getSheetName(sheetIndex).toUpperCase();
        Map<String, Object> dataMap = getDataFromExcel(file, sheetIndex, projectId);
        if (!(boolean) dataMap.get(SUCCESS)) {
          droptableHandle(li, projectId);
          return dataMap;
        }
        List<Object> fielCnListC = (List<Object>) dataMap.get(FILELIST);
        List<Object> fielListC = (List<Object>) dataMap.get(FILELISTC);
        List<Object> fielTypeListC = (List<Object>) dataMap.get(FILETYPELIST);
        List<List<Object>> dataList = (List<List<Object>>) dataMap.get(DATALIST);
        map = checkList((List<String>) dataMap.get(LIST1), tableName);
        if (!(boolean) map.get(SUCCESS)) {
          droptableHandle(li, projectId);
          return map;
        }
        // 建表
        Map<String, Object> createmap =
            tableCreate(tableName, fielCnListC, fielListC, fielTypeListC, projectId);
        if (!(boolean) createmap.get(SUCCESS)) {
          droptableHandle(li, projectId);
          return createmap;
        }
        li.add(tableName);
        Map<String, Object> insertmap = insertData(dataList, fielListC, tableName, projectId);
        if (!(boolean) insertmap.get(SUCCESS)) {
          droptableHandle(li, projectId);
          return insertmap;
        }
      }
    } catch (Exception e) {
      droptableHandle(li, projectId);
      map.put(SUCCESS, false);
      map.put(MESSAGE, "表" + tableName + e.getLocalizedMessage());
      logger.debug("数据插入失败", e);
    }

    return map;
  }




  @DynamicDS("projectId")
  public void droptableHandle(List<String> li, String projectId) {
    this.droptableHandle(li);
  }

  public void droptableHandle(List<String> li) {
    for (int i = 0; i < li.size(); i++) {
      String dropTable = DROPTBALE + li.get(i).toUpperCase().trim() + "";
      excelBasicMapper.tableDrop(dropTable.trim());
    }
  }

  @DynamicDS("projectId")
  public Map<String, Object> getDataFromExcel(File file, int sheetIndex, String projectId)
      throws IOException {
    return this.getDataFromExcel(file, sheetIndex);
  }

  public void handleData(List<Object> fielCnListC, Map<String, Object> map) {
    if (fielCnListC != null && !fielCnListC.isEmpty()) {
      for (int i = 0; i < fielCnListC.size(); i++) {
        if (EmptyUtil.isEmpty(fielCnListC.get(i))) {
          map.put(SUCCESS, false);
          map.put(MESSAGE, TSH);
          return;
        }
      }
    }
  }

  public Map<String, Object> getDataFromExcel(File file, int sheetIndex) throws IOException {
    Map<String, Object> map = new HashMap<>();
    map.put(SUCCESS, true);
    map.put(MESSAGE, TSHTHREE);
    List<Object> fielCnListC = null;
    List<Object> fielListC = null;
    List<Object> fielTypeListC = null;
    // 字段名
    List<List<Object>> fielCnList = ExcelUtil.importExcelXLSXRange(file, 0, sheetIndex);
    // 字段名
    List<List<Object>> fielList = ExcelUtil.importExcelXLSXRange(file, 1, sheetIndex);
    // 字段類型
    List<List<Object>> fielTypeList = ExcelUtil.importExcelXLSXRange(file, 2, sheetIndex);
    // 数据
    List<List<Object>> dataList = ExcelUtil.importExcelXLSX(file, 3, sheetIndex);

    List<String> list1 = new ArrayList<>();
    if (fielCnList != null && !fielCnList.isEmpty()) {
      fielCnListC = fielCnList.get(0);
      handleData(fielCnListC, map);
    }
    if (!CollectionUtils.isEmpty(fielList)) {
      fielListC = fielList.get(0);
      handleData(fielListC, map);
      if ((boolean) map.get(SUCCESS)) {
        for (Object tmp : fielListC) {
          list1.add(tmp.toString().trim().toUpperCase());
        }
      } else {
        map.put(MESSAGE, TSH);
        return map;
      }

    }
    if (!CollectionUtils.isEmpty(fielTypeList)) {
      fielTypeListC = fielTypeList.get(0);
      this.isFileTypeListEmpty(fielTypeList, map);
    }

    map.put(FILELIST, fielCnListC);
    map.put(FILELISTC, fielListC);
    map.put(FILETYPELIST, fielTypeListC);
    map.put("fielCnList", fielCnList);
    map.put("fielList", fielList);
    map.put(DATALIST, dataList);
    map.put(LIST1, list1);
    return map;
  }

  private void isFileTypeListEmpty(List<List<Object>> fielTypeList, Map<String, Object> map) {
    for (int i = 0; i < fielTypeList.size(); i++) {
      if (EmptyUtil.isEmpty(fielTypeList.get(i))) {
        map.put(SUCCESS, false);
        map.put(MESSAGE, TSH);
        break;
      }
    }
  }


  public String listToString(List list, char separator) {
    StringBuilder sb = new StringBuilder();
    for (int i = 0; i < list.size(); i++) {
      sb.append(list.get(i)).append(separator);
    }
    return list.isEmpty() ? "" : sb.toString().substring(0, sb.toString().length() - 1);
  }

  public Map<String, Object> insertData(List<List<Object>> dataList, List<Object> fielListC,
      String tableName, String projectId) {
    try {
      DynamicDataSourceContextHolder.setDataSourceType(projectId);
      return this.insertData(dataList, fielListC, tableName);
    } finally {
      DynamicDataSourceContextHolder.clearDataSource();
    }
  }

  public Map<String, Object> insertData(List<List<Object>> dataList, List<Object> fielListC,
      String tableName) {


    Map<String, Object> map = new HashMap<>();
    map.put(SUCCESS, true);
    Map<String, Object> insertMap = new HashMap<>();
    // 拼接的SQL,作为insert语句的一部分
    String lineColumn = listToString(fielListC, ',');
    if (dataList != null) {
      for (int i = 0; i < dataList.size(); i++) {
        insertMap.put("tableName", tableName);
        insertMap.put("lineColumn", lineColumn);
        insertMap.put("lineList", dataList.get(i));
        try {
          excelBasicMapper.insertData(insertMap);
        } catch (Exception e) {
          throw new CustomException("插入数据失败异常如下：" + e.getLocalizedMessage());
        }
      }
    }
    return map;

  }

  public List<Object> handleList(List<Object> list, List<Object> fielTypeListC) {
    List<Object> newList = new ArrayList<>();
    for (int i = 0; i < list.size(); i++) {
      if ("date".equals(fielTypeListC.get(i))) {
        newList.add("to_date('" + list.get(i) + "','yyyy-MM')");
      } else {
        newList.add(list.get(i));
      }
    }
    return newList;

  }


  public Map<String, Object> tableCreate(String tableName, List<Object> fielCnList,
      List<Object> fielListC, List<Object> fielTypeListC, String projectId) {
    Map<String, Object> map = new HashMap<>();
    map.put(SUCCESS, true);
    boolean isExist = false;
    boolean isOp = false;
    isExist = ((ExcelServiceImpl) AopContext.currentProxy()).checkDb(tableName, projectId);
    isOp = checkOp(tableName, projectId);
    // 如果此表被操作過
    if (!isOp) {
      try {
        ((ExcelServiceImpl) AopContext.currentProxy()).createTableSql(tableName, isExist,
            fielCnList, fielListC, fielTypeListC, projectId);
      } catch (Exception e) {
        LogUtils.error(e.getMessage());
        throw new CustomException("建表失败异常如下：" + e.getLocalizedMessage());
      }

    } else {
      map.put(SUCCESS, false);
      map.put(MESSAGE, "保存失败," + tableName + "表已被占用");
    }

    return map;
  }



  @DynamicDS("projectId")
  public void createTableSql(String tableName, boolean isExist, List<Object> fielCnList,
      List<Object> fielListC, List<Object> fielTypeListC, String projectId) {

    // 如果查询到日志操作记录表里面沒有这个操作记录
    String sql = "";
    String createSql = "CREATE TABLE " + tableName + "(";
    StringBuilder bldSql = new StringBuilder();
    String dropTable = DROPTBALE + tableName.toUpperCase().trim() + "";
    // 如果存在此表則刪除
    if (isExist) {
      excelBasicMapper.tableDrop(dropTable.trim());
    }
    for (int i = 0; i < fielListC.size(); i++) {
      bldSql.append(fielListC.get(i) + " " + fielTypeListC.get(i) + ",");
    }
    sql = createSql + bldSql.toString() + "IsTest varchar2(10) default '0')";
    excelBasicMapper.tableBuild(sql.trim());
    if (fielCnList != null) {
      for (int i = 0; i < fielCnList.size(); i++) {
        if (!StringUtil.isEmpty(fielCnList.get(i).toString())) {
          String commentSql = "comment on column " + tableName.toUpperCase().trim() + "."
              + fielListC.get(i) + " is '" + fielCnList.get(i).toString().trim() + "'";
          excelBasicMapper.tableComment(commentSql.trim());

        }

      }
    }

  }

  @DynamicDS("projectId")
  public boolean checkDb(String tableName, String projectId) {
    return this.checkDb(tableName);
  }

  public boolean checkDb(String tableName) {
    boolean isExist = false;
    Map<String, Object> tableList = excelBasicMapper.isTableExist(tableName.toUpperCase());
    if (tableList != null) {
      isExist =
          tableName.toUpperCase().trim().equals(tableList.get(TABLENAME.toUpperCase().trim()) + "");
    }
    return isExist;
  }

  public boolean checkOp(String tableName, String projectId) {
    boolean isExist = false;
    Map<String, Object> tableList =
        excelBasicMapper.isTableExistAn(tableName.toUpperCase(), projectId);
    if (tableList != null) {
      isExist =
          tableName.toUpperCase().trim().equals(tableList.get(TABLENAME.toUpperCase().trim()) + "");
    }
    return isExist;
  }

  public void closeConnection(Connection conn) {
    if (conn != null) {
      try {
        conn.close();
      } catch (SQLException e) {
        logger.debug(SJKLJGBSB, e);
      }
    }
  }

  public void closePrepareStatement(PreparedStatement ps) {
    if (ps != null) {
      try {
        ps.close();
      } catch (SQLException e) {
        logger.debug(YBYSB, e);
      }
    }
  }

  public void closeResultSet(ResultSet rs) {
    if (rs != null) {
      try {
        rs.close();
      } catch (SQLException e) {
        logger.debug(JGJGBSB, e);
      }
    }
  }

  public void rollBack(Connection conn) {
    try {
      conn.rollback();
    } catch (SQLException e1) {
      logger.debug("数据rollback失败", e1);

    }
  }

  /**
   * 动态生成插入语句
   *
   * @return
   */
  public String insertSqlBuild(String tableName, List<Object> titles) {
    StringBuilder insertSql = new StringBuilder();
    StringBuilder columns = new StringBuilder();
    StringBuilder values = new StringBuilder();
    columns.append("INSERT INTO " + tableName + " (");
    values.append("VALUES(");
    for (int i = 0; i < titles.size(); i++) {
      if (i == titles.size() - 1) {
        columns = columns.append(titles.get(i) + ") ");
        values = values.append("?)");
        break;
      }
      columns = columns.append(titles.get(i) + ",");
      values = values.append("?,");
    }
    columns.append(values);
    insertSql.append(columns);
    return insertSql.toString();
  }

  /**
   * 动态生成插入语句
   *
   * @return
   */
  public String insertSqlBuild(String tableName, List<Object> titles, List<Object> fielTypeListC) {
    StringBuilder insertSql = new StringBuilder();
    StringBuilder columns = new StringBuilder();
    StringBuilder values = new StringBuilder();
    columns.append("INSERT INTO " + tableName + " (");
    values.append("VALUES(");
    for (int i = 0; i < titles.size(); i++) {
      if (i == titles.size() - 1) {
        columns = columns.append(titles.get(i) + ") ");

        if ("date".equals(fielTypeListC.get(i).toString())) {
          values = values.append("to_date(?,'yyyy-MM'))");
        } else {
          values = values.append("?)");
        }
        break;
      }
      columns = columns.append(titles.get(i) + ",");
      if ("date".equals(fielTypeListC.get(i).toString())) {
        values = values.append("to_date(?,'yyyy-MM'),");
      } else {
        values = values.append("?,");
      }
    }
    columns.append(values);
    insertSql.append(columns);
    return insertSql.toString();
  }

  /**
   * 判断表是否存在，若存在则跳过，若不存在则建立
   * 
   * @param conn
   * @param tableName
   * @return
   */
  public boolean tableDelete(Connection conn, String tableName) {
    boolean isSucc = false;
    PreparedStatement ps = null;
    PreparedStatement ps2 = null;
    boolean isExist = true;
    boolean isOp = true;
    ResultSet rs = null;
    ResultSet rs2 = null;
    String tableCheck = "select table_name from user_tables where table_name='"
        + tableName.toUpperCase().trim() + "'";
    String tableIfOp =
        "select table_name from PKG_MGT where table_name='" + tableName.toUpperCase().trim() + "'";
    String dropTable = DROPTBALE + tableName.toUpperCase().trim() + "";
    try {
      ps = conn.prepareStatement(tableCheck);
      rs = ps.executeQuery();
      while (rs.next()) {
        isExist = tableName.toUpperCase().trim().equals(rs.getString(TABLENAME));
        isSucc = true;
      }
      ps2 = conn.prepareStatement(tableIfOp);
      rs2 = ps.executeQuery();
      while (rs2.next()) {
        isOp = tableName.toUpperCase().trim().equals(rs2.getString(TABLENAME));
        isSucc = true;
      }
      // 如果此表被操作過
      if (isOp) {
        isSucc = false;
        return isSucc;
      } else if (isExist) {
        // 如果查询到日志操作记录表里面沒有这个操作记录
        // 如果存在此表則刪除
        ps.executeUpdate(dropTable.trim());
      }
    } catch (SQLException e) {
      logger.debug(JBYJYC, e);
      isSucc = false;
      return isSucc;
    } finally {
      closePrepareStatement(ps);
      closePrepareStatement(ps2);
      closeResultSet(rs);
      closeResultSet(rs2);
      closeConnection(conn);
    }
    return isSucc;
  }


}
